CREATE TABLE [dbo].[Product_Type]
(
[PROD_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_PROD_TYPE] DEFAULT (''),
[DESCRIPTION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_DESCRIPTION] DEFAULT (''),
[SALES_ITEM] [bit] NOT NULL CONSTRAINT [DF_Product_Type_SALES_ITEM] DEFAULT ((0)),
[DUES_SUB_ITEM] [bit] NOT NULL CONSTRAINT [DF_Product_Type_DUES_SUB_ITEM] DEFAULT ((0)),
[PROFILE_SORT] [int] NOT NULL CONSTRAINT [DF_Product_Type_PROFILE_SORT] DEFAULT ((0)),
[SUBTYPE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_SUBTYPE_PROMPT] DEFAULT (''),
[DESCRIPTION_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_DESCRIPTION_PROMPT] DEFAULT (''),
[EFF_DATE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_EFF_DATE_PROMPT] DEFAULT (''),
[THRU_DATE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_THRU_DATE_PROMPT] DEFAULT (''),
[SOURCE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_SOURCE_PROMPT] DEFAULT (''),
[AMOUNT_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_AMOUNT_PROMPT] DEFAULT (''),
[TICKLER_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_TICKLER_PROMPT] DEFAULT (''),
[ACTION_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_ACTION_PROMPT] DEFAULT (''),
[CEU_TYPE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_CEU_TYPE_PROMPT] DEFAULT (''),
[UNITS_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UNITS_PROMPT] DEFAULT (''),
[USER_EDIT] [bit] NOT NULL CONSTRAINT [DF_Product_Type_USER_EDIT] DEFAULT ((0)),
[RETAIN_MONTHS] [int] NOT NULL CONSTRAINT [DF_Product_Type_RETAIN_MONTHS] DEFAULT ((0)),
[FOLLOW_UP_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_FOLLOW_UP_PROMPT] DEFAULT (''),
[NOTE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_NOTE_PROMPT] DEFAULT (''),
[EXTENDED_DEMO] [bit] NOT NULL CONSTRAINT [DF_Product_Type_EXTENDED_DEMO] DEFAULT ((0)),
[UF_1_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_1_PROMPT] DEFAULT (''),
[UF_2_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_2_PROMPT] DEFAULT (''),
[UF_3_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_3_PROMPT] DEFAULT (''),
[UF_4_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_4_PROMPT] DEFAULT (''),
[UF_5_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_5_PROMPT] DEFAULT (''),
[UF_6_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_6_PROMPT] DEFAULT (''),
[UF_7_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_UF_7_PROMPT] DEFAULT (''),
[CO_ID_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_CO_ID_PROMPT] DEFAULT (''),
[OTHER_CODE_PROMPT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_OTHER_CODE_PROMPT] DEFAULT (''),
[ACCESS_KEYWORDS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_ACCESS_KEYWORDS] DEFAULT (''),
[CREATE_HISTORY_TAB] [bit] NOT NULL CONSTRAINT [DF_Product_Type_CREATE_HISTORY_TAB] DEFAULT ((0)),
[HISTORY_TAB_TITLE] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Product_Type_HISTORY_TAB_TITLE] DEFAULT (''),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_Product_Type_Delete]
    ON [dbo].[Product_Type]
    FOR DELETE
AS
BEGIN
    DELETE Security_Tables
    WHERE TABLE_NAME = (SELECT 'Activity_' + PROD_TYPE FROM deleted)

    DELETE Security_Filters
    WHERE TABLE_NAME = (SELECT 'Activity_' + PROD_TYPE FROM deleted)
END

GO
CREATE TRIGGER [dbo].[asi_Product_Type_Insert]
    ON [dbo].[Product_Type]
    FOR INSERT
AS
BEGIN
    INSERT Security_Tables(MENU_NAME,TABLE_NAME)
    SELECT 'Activity - ' + PROD_TYPE, 'Activity_' + PROD_TYPE
    FROM inserted

    INSERT Security_Filters (SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT sg.SECURITY_GROUP,'BROWSE', 'Activity_' + i.PROD_TYPE
    FROM inserted i,  Security_Groups sg

    INSERT Security_Filters (SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT sg.SECURITY_GROUP,'EDIT', 'Activity_' + i.PROD_TYPE
    FROM inserted i,  Security_Groups sg

    INSERT Security_Filters (SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT sg.SECURITY_GROUP,'INSERT', 'Activity_' + i.PROD_TYPE
    FROM inserted i,  Security_Groups sg

    INSERT Security_Filters (SECURITY_GROUP, SECURITY_MODE, TABLE_NAME)
    SELECT sg.SECURITY_GROUP,'DELETE', 'Activity_' + i.PROD_TYPE
    FROM inserted i,  Security_Groups sg

    UPDATE Security_Filters SET TABLE_ACCESSIBLE = 1
    WHERE SECURITY_GROUP = 'Administrator'
          AND TABLE_NAME = (SELECT 'Activity_' + i.PROD_TYPE FROM inserted i)
END

GO
ALTER TABLE [dbo].[Product_Type] ADD CONSTRAINT [PK_Product_Type] PRIMARY KEY CLUSTERED ([PROD_TYPE]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Product_Type] TO [IMIS]
GRANT SELECT ON  [dbo].[Product_Type] TO [IMIS]
GRANT INSERT ON  [dbo].[Product_Type] TO [IMIS]
GRANT DELETE ON  [dbo].[Product_Type] TO [IMIS]
GRANT UPDATE ON  [dbo].[Product_Type] TO [IMIS]
GO